package com.task.www.dao;

import com.mysql.cj.jdbc.JdbcConnection;
import com.task.www.po.Food;
import com.task.www.util.JDBCUtilsMc;
import com.task.www.util.JDBCUtilsUs;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class UserData {
    private Connection conn = null;
    private String sql = null;
    private PreparedStatement prep = null;
    private ResultSet rs = null;


    public void insertregister(String accout, String password) {
        try {
            conn = JDBCUtilsUs.getConnection();
            sql = "insert into register values(null,?,?,0)";
            prep = conn.prepareStatement(sql);
            prep.setString(1, accout);
            prep.setString(2, password);
            prep.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsUs.close(prep, conn);
        }
    }

    public boolean selectaccout(String accout, String password) {
        try {
            conn = JDBCUtilsUs.getConnection();
            sql = "select accout password from register where accout = ? and password = ?";
            prep = conn.prepareStatement(sql);
            prep.setString(1, accout);
            prep.setString(2, password);
            rs = prep.executeQuery();
            return rs.next();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsUs.close(prep, conn, rs);
        }
        return false;
    }

    public int selectids(String accout) {
        try {
            conn = JDBCUtilsUs.getConnection();
            sql = "select ids from register where accout = ?";
            prep = conn.prepareStatement(sql);
            prep.setString(1, accout);
            rs = prep.executeQuery();
            int i = 0;
            while (rs.next()) {
                i = rs.getInt(1);
            }
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsUs.close(prep, conn, rs);
        }
        return 0;
    }

    public int selectmonney(int ids) {
        try {
            conn = JDBCUtilsUs.getConnection();
            sql = "select monney from register where ids = ?";
            prep = conn.prepareStatement(sql);
            prep.setInt(1, ids);
            rs = prep.executeQuery();
            int i = 0;
            while (rs.next()) {
                i = rs.getInt(1);
            }
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsUs.close(prep, conn, rs);
        }
        return 0;
    }

    public void updatemonney(int ids, int monneyadd) {
        int monney = selectmonney(ids);
        monneyadd = monneyadd + monney;
        try {
            conn = JDBCUtilsUs.getConnection();
            sql = "update register set monney = ? where ids = ?";
            prep = conn.prepareStatement(sql);
            prep.setInt(1, monneyadd);
            prep.setInt(2, ids);
            prep.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsUs.close(prep, conn);
        }
    }

    //通过店名找到id
    public int selectidsmer(String restaurant_name) {
        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "select ids from register where restaurant_name = ?";
            prep = conn.prepareStatement(sql);
            prep.setString(1, restaurant_name);
            rs = prep.executeQuery();
            int i = 0;
            while (rs.next()) {
                i = rs.getInt(1);
            }
            return i;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsUs.close(prep, conn, rs);
        }
        return 0;
    }

    //遍历商家
    public ArrayList<String> selectmerchant() {
        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "select restaurant_name,restaurant_introduce from register";
            prep = conn.prepareStatement(sql);
            rs = prep.executeQuery();
            ArrayList<String> merchants = new ArrayList<>();
            while (rs.next()) {
                //店名为空跳过
                if (rs.getString(1) == null) {
                    continue;
                }
                merchants.add(rs.getString(1));
                merchants.add(rs.getString(2));
            }
            return merchants;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsMc.close(prep, conn, rs);
        }
        return null;
    }

    //通过ids获取商家
    public ArrayList<String> selectmerchant(int ids) {
        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "select restaurant_name,restaurant_introduce from register where ids = ?";
            prep = conn.prepareStatement(sql);
            prep.setInt(1, ids);
            rs = prep.executeQuery();
            ArrayList<String> merchant = new ArrayList<>();
            while (rs.next()) {
                merchant.add(rs.getString(1));
                merchant.add(rs.getString(2));
            }
            return merchant;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsMc.close(prep, conn, rs);
        }
        return null;
    }

    //通过ids获取优惠券
    public ArrayList<Integer> selectcoupon(int ids) {
        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "select up,down from coupon where fkid = ?";
            prep = conn.prepareStatement(sql);
            prep.setInt(1, ids);
            rs = prep.executeQuery();
            ArrayList<Integer> coupons = new ArrayList<>();
            while (rs.next()) {
                coupons.add(rs.getInt(1));
                coupons.add(rs.getInt(2));
            }
            return coupons;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsMc.close(prep, conn, rs);
        }
        return null;
    }

    //通过ids获取菜单
    public ArrayList<Food> selectfood(int ids) {
        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "select name,price,introduce,comment from food where fkid = ?";
            prep = conn.prepareStatement(sql);
            prep.setInt(1, ids);
            rs = prep.executeQuery();
            ArrayList<Food> foods = new ArrayList<>();
            while (rs.next()) {
                foods.add(new Food(rs.getString(1), rs.getInt(2), rs.getNString(3), rs.getString(4)));
            }
            return foods;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsMc.close(prep, conn, rs);
        }
        return null;
    }

    //查看用户是否领取优惠券
    public boolean havecoupon(int ids, int idsmenchant) {
        try {
            conn = JDBCUtilsUs.getConnection();
            sql = "select up from coupon where fkidu = ? and fkidm = ?";
            prep = conn.prepareStatement(sql);
            prep.setInt(1, ids);
            prep.setInt(2, idsmenchant);
            rs = prep.executeQuery();
            if (rs.next()) {
                return true;
            }
            return false;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsMc.close(prep, conn, rs);
        }
        return false;
    }

    //领取优惠券
    public void getcoupon(int ids, int idsmenchant, ArrayList<Integer> coupon) {
        try {
            conn = JDBCUtilsUs.getConnection();
            sql = "insert into coupon values(null,?,?,?,?)";
            for (int i = 0; i < coupon.size(); i++) {
                prep = conn.prepareStatement(sql);
                prep.setInt(3, ids);
                prep.setInt(4, idsmenchant);
                prep.setInt(1, coupon.get(i));
                prep.setInt(2, coupon.get(++i));
                prep.executeUpdate();
                prep.close();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsUs.close(prep, conn);
        }
    }

    //获取food的id
    public int getfoodid(int ids, String name) {
        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "select id from food where fkid = ? and name = ?";
            prep = conn.prepareStatement(sql);
            prep.setInt(1, ids);
            prep.setString(2, name);
            rs = prep.executeQuery();
            int idfood = 0;
            while (rs.next()) {
                idfood = rs.getInt(1);
            }
            return idfood;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsMc.close(prep, conn, rs);
        }
        return 0;
    }

    //写评论
    public void insertcomment(int fkid_food, int fkid_user, String comments) {
        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "insert into comment values(null,?,?,?)";
            prep = conn.prepareStatement(sql);
            prep.setInt(1, fkid_food);
            prep.setInt(2, fkid_user);
            prep.setString(3, comments);
            prep.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsMc.close(prep, conn);
        }
    }

    //获取最大优惠金额的优惠券
    public int[] getdiscount(int buyup, int fkidu, int fkidm) {
        try {
            conn = JDBCUtilsUs.getConnection();
            sql = "select up,down from coupon where up < ? and fkidu = ? and fkidm = ?";
            prep = conn.prepareStatement(sql);
            prep.setInt(2, fkidu);
            prep.setInt(3, fkidm);
            prep.setInt(1, buyup);
            rs = prep.executeQuery();
            int[] updown = {0,0};
            ArrayList<Integer> list = new ArrayList<>();
            while (rs.next()) {
                if(rs.getInt(2)>updown[1]){
                    updown[0] = rs.getInt(1);
                    updown[1] = rs.getInt(2);
                }
            }
            return updown;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsMc.close(prep, conn,rs);
        }
        return null;
    }


    //顾客扣钱，商家加前
    public boolean buy(int monney ,int idsu,int idsm){
        int monneyuser = 0;
        int monneymerchant = 0;
        int wait = 0;
        try {
            conn = JDBCUtilsUs.getConnection();
            sql = "select monney from register where ids = ?";
            prep = conn.prepareStatement(sql);
            prep.setInt(1, idsu);
            rs = prep.executeQuery();
            while (rs.next()) {
                monneyuser = rs.getInt(1);
                    if(rs.getInt(1)<monney) {
                        return false;
                    }
                }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsMc.close(prep, conn,rs);
        }

        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "select monney,wait from register where ids = ?";
            prep = conn.prepareStatement(sql);
            prep.setInt(1, idsm);
            rs = prep.executeQuery();
            while (rs.next()) {
                monneymerchant = rs.getInt(1);
                wait = rs.getInt(2);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsMc.close(prep, conn,rs);
        }

        try {
            conn = JDBCUtilsUs.getConnection();
            sql = "update register set monney = ? where ids = ?";
            prep = conn.prepareStatement(sql);
            prep.setInt(1,monneyuser-monney);
            prep.setInt(2, idsu);
            prep.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsMc.close(prep, conn);
        }

        try {
            conn = JDBCUtilsMc.getConnection();
            sql = "update register set monney = ?,wait = ? where ids = ?";
            prep = conn.prepareStatement(sql);
            prep.setInt(1,monneymerchant+monney);
            prep.setInt(2, wait+1);
            prep.setInt(3, idsm);
            prep.executeUpdate();
            return true;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsMc.close(prep, conn);
        }

        return false;
    }

    //添加订单
    public void addorder (int fkidu ,int fkidm,String name){
        try {
            conn = JDBCUtilsUs.getConnection();
            sql = "insert into buy values(null,?,?,false,?)";
            prep = conn.prepareStatement(sql);
            prep.setInt(1,fkidu);
            prep.setInt(2, fkidm);
            prep.setString(3,name);
            prep.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsMc.close(prep, conn);
        }
    }

    //查找订单
    public ArrayList<String> checkorder(int fkidu) {
        try {
            conn = JDBCUtilsUs.getConnection();
            sql = "select name ,boolean from buy where fkidu = ?";
            prep = conn.prepareStatement(sql);
            prep.setInt(1, fkidu);
            rs = prep.executeQuery();
            ArrayList<String> buys = new ArrayList<>();
            while(rs.next()){
                buys.add(rs.getString(1));
                buys.add(String.valueOf(rs.getBoolean(2)));
            }
            return buys;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JDBCUtilsMc.close(prep, conn,rs);
        }
        return null;
    }
}
